/*
 * File DownLoadDetailDB.java
 * Copyright閿涙opyright 2008-2014 CiWong.Inc. All Rights Reserved.
 * Description閿燂拷DownLoadDetailDB.java
 * Modify By閿涙es-jianpingliu
 * Modify Date閿燂拷014-4-8
 * Modify Type閿涙dd
 */
package com.ciwong.epaper.util.download;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;

import java.util.ArrayList;
import java.util.List;

/**
 * 下载数据库
 *
 * @author res-jianpingliu
 * @version ciwong v.1.0 2014-4-8
 * @since ciwong v.1.0
 */
public class DownLoadDB {
    private static final String TAG = "DownLoadDB";
    private static Context mContext;

    private static SQLiteDatabase mDb;

    private static DownLoadHelper helper;

    private static final String SYNC_TAG = "SYNC_TAG";

    public static void setContext(Context con) {
        mContext = con;
    }

    /**
     *
     */
    private static synchronized void getWritableDB() {
        if (helper == null) {
            helper = new DownLoadHelper(mContext);
        }
        mDb = helper.getWritableDatabase();
    }

    /**
     */
    private static synchronized void getReadableDB() {
        if (helper == null) {
            helper = new DownLoadHelper(mContext);
        }
        mDb = helper.getReadableDatabase();
    }

    /**
     * 获取值
     *
     * @param cur
     * @return
     */
    private static DownLoadInfo getDetail(Cursor cur) {
        DownLoadInfo bean = new DownLoadInfo();
        bean.set_id(cur.getLong(cur.getColumnIndex(DownLoadTable._ID)));
        bean.setUrl(cur.getString(cur.getColumnIndex(DownLoadTable.URL)));
        bean.setIconUrl(
                cur.getString(cur.getColumnIndex(DownLoadTable.ICON_URL)));
        bean.setStatus(cur.getInt(cur.getColumnIndex(DownLoadTable.STATUS)));
        bean.setType(cur.getInt(cur.getColumnIndex(DownLoadTable.TYPE)));
        bean.setProgress(
                cur.getInt(cur.getColumnIndex(DownLoadTable.PROGRESS)));
        bean.setSavePath(
                cur.getString(cur.getColumnIndex(DownLoadTable.SAVE_PATH)));
        bean.setLength(cur.getLong(cur.getColumnIndex(DownLoadTable.LENGTH)));
        bean.setKey(cur.getString(cur.getColumnIndex(DownLoadTable.KEY)));
        bean.setBookId(
                cur.getString(cur.getColumnIndex(DownLoadTable.BOOK_ID)));
        bean.setChapterId(
                cur.getString(cur.getColumnIndex(DownLoadTable.CHAPTER_ID)));
        bean.setModuleId(
                cur.getString(cur.getColumnIndex(DownLoadTable.MODULE_ID)));
        bean.setVersionId(
                cur.getString(cur.getColumnIndex(DownLoadTable.VERSION_ID)));
        bean.setBookName(
                cur.getString(cur.getColumnIndex(DownLoadTable.BOOK_NAME)));
        bean.setChapterName(
                cur.getString(cur.getColumnIndex(DownLoadTable.CHAPTER_NAME)));
        bean.setSize(cur.getString(cur.getColumnIndex(DownLoadTable.SIZE)));
        bean.setIsFree(cur.getInt(cur.getColumnIndex(DownLoadTable.IS_FREE)));
        bean.setIsKaoShi(cur.getInt(cur.getColumnIndex(DownLoadTable.IS_KAOSHI)));
        bean.setHash(cur.getString(cur.getColumnIndex(DownLoadTable.HASH)));
        bean.setResourceName(cur.getString(cur.getColumnIndex(DownLoadTable.RESOURCENAME)));
        bean.setResourceType(cur.getString(cur.getColumnIndex(DownLoadTable.RESOURCETYPE)));
        bean.setSubjectId(cur.getInt(cur.getColumnIndex(DownLoadTable.SUBJECTID)));
        return bean;
    }

    /**
     * 获取ContentValues
     *
     * @param bean
     * @return
     */
    public static ContentValues getContentValue(DownLoadInfo bean) {
        ContentValues values = new ContentValues();
        values.put(DownLoadTable.ICON_URL, bean.getIconUrl());
        values.put(DownLoadTable.STATUS, bean.getStatus());
        values.put(DownLoadTable.TYPE, bean.getType());
        values.put(DownLoadTable.PROGRESS, bean.getProgress());
        values.put(DownLoadTable.SAVE_PATH, bean.getSavePath());
        values.put(DownLoadTable.URL, bean.getUrl());
        values.put(DownLoadTable.LENGTH, bean.getLength());
        values.put(DownLoadTable.KEY, bean.getKey());
        values.put(DownLoadTable.SIZE, bean.getSize());
        values.put(DownLoadTable.BOOK_ID, bean.getBookId());
        values.put(DownLoadTable.MODULE_ID, bean.getModuleId());
        values.put(DownLoadTable.VERSION_ID, bean.getVersionId());
        values.put(DownLoadTable.BOOK_NAME, bean.getBookName());
        values.put(DownLoadTable.CHAPTER_ID, bean.getChapterId());
        values.put(DownLoadTable.CHAPTER_NAME, bean.getChapterName());
        values.put(DownLoadTable.IS_FREE, bean.getIsFree());
        values.put(DownLoadTable.IS_KAOSHI, bean.getIsKaoShi());
        values.put(DownLoadTable.HASH, bean.getHash());
        values.put(DownLoadTable.RESOURCENAME, bean.getResourceName());
        values.put(DownLoadTable.RESOURCETYPE, bean.getResourceType());
        values.put(DownLoadTable.SUBJECTID, bean.getSubjectId());
        return values;
    }

    /**
     * 插入
     *
     * @param bean
     */
    public static long insertDetail(DownLoadInfo bean) {
        long id = 0;

        try {
            synchronized (SYNC_TAG) {
                getWritableDB();
                Cursor cur;
                String selection;
                String[] selectionArgs;
                if (bean.getType() == 1) {
                    selection = DownLoadTable.BOOK_ID + " = ? and "
                            + DownLoadTable.CHAPTER_ID + " = ? and "
                            + DownLoadTable.MODULE_ID + " = ? and "
                            + DownLoadTable.VERSION_ID + " = ?";
                    selectionArgs = new String[]{bean.getBookId(),
                            bean.getChapterId(), bean.getModuleId(), bean.getVersionId()};
                    cur = mDb.query(DownLoadTable.TABLE_NAME,
                            new String[]{DownLoadTable._ID}, selection, selectionArgs,
                            null, null, null);
                } else {
                    selection = DownLoadTable.BOOK_ID + " = ? and "
                            + DownLoadTable.CHAPTER_ID + " = ? and "
                            + DownLoadTable.TYPE + " = ?";
                    selectionArgs = new String[]{bean.getBookId(),
                            bean.getChapterId(), "" + bean.getType()};
                    cur = mDb.query(DownLoadTable.TABLE_NAME,
                            new String[]{DownLoadTable._ID}, selection, selectionArgs,
                            null, null, null);


                }

                if (cur.getCount() > 0) {
                    cur.moveToFirst();
                    id = cur.getLong(0);
                    mDb.update(DownLoadTable.TABLE_NAME, getContentValue(bean),
                            selection, selectionArgs);
                } else {
                    ContentValues values = getContentValue(bean);
                    id = mDb.insert(DownLoadTable.TABLE_NAME, null, values);
                }
                cur.close();
            }

        } catch (SQLException e) {
            e.getStackTrace();
        }

        return id;
    }

    /**
     * 通过书籍id查询章节列表
     *
     * @param bookId 书籍ID
     * @return 章节列表
     */
    public static List<DownLoadInfo> queryListByBookId(String bookId, int type) {
        List<DownLoadInfo> list = new ArrayList<DownLoadInfo>();
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cur = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.BOOK_ID + "= ?and "
                                + DownLoadTable.TYPE + "=?", new String[]{bookId, "" + type},
                        null, null, null);
                while (cur.moveToNext()) {
                    DownLoadInfo info = getDetail(cur);
                    list.add(info);
                }
                cur.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 查询所有正在下载、等待下载、暂停下载及下载失败的章节
     */
    public static List<DownLoadInfo> queryUnCompletedList() {
        List<DownLoadInfo> list = new ArrayList<DownLoadInfo>();

        try {

            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cur = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.STATUS + "= ?" + " or " + DownLoadTable.STATUS
                                + " = ?" + " or " + DownLoadTable.STATUS + " = ?"
                                + " or " + DownLoadTable.STATUS + "= ?" + " or "
                                + DownLoadTable.STATUS + "= ?",
                        new String[]{"" + DownloadStatus.STATUS_LOADING,
                                "" + DownloadStatus.STATUS_PAUSE,
                                "" + DownloadStatus.STATUS_FAILED,
                                "" + DownloadStatus.STATUS_DOWNLOADING,
                                "" + DownloadStatus.STATUS_UPZIPING},
                        null, null, null);
                while (cur.moveToNext()) {
                    DownLoadInfo info = getDetail(cur);
                    list.add(info);
                }
                cur.close();
                dispose();
            }


        } catch (SQLException e) {
            e.getStackTrace();
        }
        return list;
    }

    /**
     * 查询所有已经下载完成的章节列表
     */
    public static List<DownLoadInfo> queryCompletedList() {
        List<DownLoadInfo> list = new ArrayList<DownLoadInfo>();
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cur = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.STATUS + " = ?",
                        new String[]{DownloadStatus.STATUS_COMPLETED + ""},
                        null, null, DownLoadTable.CHAPTER_ID);
                while (cur.moveToNext()) {
                    DownLoadInfo info = getDetail(cur);
                    list.add(info);
                }
                cur.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 通过二维码扫描获取的key查询本地是否保存资源包
     */
    public static DownLoadInfo queryDownloadInfoByKey(String key) {
        DownLoadInfo info = null;
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                String sql = "select * from " + DownLoadTable.TABLE_NAME
                        + " where " + DownLoadTable.KEY + " like '%" + key
                        + ",%'";
                Cursor cursor = mDb.rawQuery(sql, null);
                // Cursor cursor = mDb.query(DownLoadTable.TABLE_NAME,
                // DownLoadTable.getColumns(),
                // DownLoadTable.KEY + " like '?%'", new String[]{key}, null,
                // null, null);
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    info = getDetail(cursor);
                }
                cursor.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return info;
    }


    /**
     * 通过二维码扫描获取的key查询本地是否保存资源包
     * 新资源
     */
    public static List<DownLoadInfo> queryNewDownloadInfoByKey(String key) {
        List<DownLoadInfo> list = new ArrayList<DownLoadInfo>();
        DownLoadInfo info = null;
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                String sql = "select * from " + DownLoadTable.TABLE_NAME
                        + " where " + DownLoadTable.KEY + " like '%" + key
                        + ",%'";
                Cursor cursor = mDb.rawQuery(sql, null);
                while (cursor.moveToNext()) {
                    info = getDetail(cursor);
                    list.add(info);
                }
                cursor.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 通过书籍id和章节id查询本地是否保存旧的资源资源包  old   add  type to test the old and new
     */
    public static DownLoadInfo queryDownloadInfoByBookIdAndChapterId(
            String bookId, String chapterId) {
        DownLoadInfo info = null;
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cursor = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.BOOK_ID + " = ? and "
                                + DownLoadTable.TYPE + " = ? and "
                                + DownLoadTable.CHAPTER_ID + " = ?",
                        new String[]{bookId, "" + 0, chapterId}, null, null, null);

                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    info = getDetail(cursor);
                    Log.e("MyWorkFragment", "queryDownloadInfoByBookIdAndChapterId: " + cursor.getCount() + "   " + info.getUrl());
                }
                cursor.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return info;
    }


    /**
     * 通过书籍id和章节id查询本地是否保存旧的资源资源包  old   add  type to test the old and new
     */
    public static DownLoadInfo queryDownloadInfoByBookIdAndChapterIdAndMoudleidAndVerid(
            String bookId, String chapterId, String moduleId, String versionId) {
        DownLoadInfo info = null;
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cursor = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.BOOK_ID + " = ? and "
                                + DownLoadTable.TYPE + " = ? and "
                                + DownLoadTable.MODULE_ID + " = ? and "
                                + DownLoadTable.VERSION_ID + " = ? and "
                                + DownLoadTable.CHAPTER_ID + " = ?",
                        new String[]{bookId, "" + 1, moduleId, versionId, chapterId}, null, null, null);

                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    info = getDetail(cursor);
                    Log.e("MyWorkFragment", "queryDownloadInfoByBookIdAndChapterId: " + cursor.getCount() + "   " + info.getUrl());
                }
                cursor.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return info;
    }

    public static List<DownLoadInfo> queryDownloadInfoByBookIdAndChapterId(
            String bookId, String chapterId, int type) {
        List<DownLoadInfo> list = new ArrayList<DownLoadInfo>();
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cursor = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.BOOK_ID + " = ? and "
                                + DownLoadTable.TYPE + " = ? and "
                                + DownLoadTable.CHAPTER_ID + " = ?",
                        new String[]{bookId, "" + type, chapterId}, null, null, null);

                while (cursor.moveToNext()) {
                    DownLoadInfo info = getDetail(cursor);
                    list.add(info);
                }
                cursor.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 通过书籍id和章节id查询本地是否保存资源包   新资源
     */
    public static DownLoadInfo queryDownloadInfoByBookIdAndChapterIdAndMouldAndVersionID(
            String bookId, String chapterId, String moudleId, String versionId) {
        DownLoadInfo info = null;
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cursor = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.BOOK_ID + " = ? and "
                                + DownLoadTable.MODULE_ID + " = ? and "
                                + DownLoadTable.VERSION_ID + " = ? and "
                                + DownLoadTable.CHAPTER_ID + " = ?",
                        new String[]{bookId, moudleId, versionId, chapterId}, null, null, null);
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    info = getDetail(cursor);
                    Log.e("MyWorkFragment", "queryDownloadInfoByBookIdAndChapterIdAndMouldAndVersionID: " + cursor.getCount() + "   " + info.getUrl());

                }
                cursor.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return info;
    }

    /**
     * 通过两个bookId拼接生成 单词书籍下载的查询key
     */
    public static DownLoadInfo queryDownloadInfoByDoubleBookId(String bookId) {

        DownLoadInfo info = null;
        try {
            synchronized (SYNC_TAG) {
                getReadableDB();
                Cursor cursor = mDb.query(DownLoadTable.TABLE_NAME,
                        DownLoadTable.getColumns(),
                        DownLoadTable.BOOK_ID + " = ?",
                        new String[]{bookId}, null, null, null);
                if (cursor.getCount() > 0) {
                    cursor.moveToFirst();
                    info = getDetail(cursor);
                    Log.e(TAG, "queryDownloadInfoByDoubleBookId: " + cursor.getCount() + "   " + info.getUrl());
                }
                cursor.close();
                dispose();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return info;
    }

    /**
     * 清除所有数据
     */
    public static void deleteData(DownLoadInfo dInfo) {
        synchronized (SYNC_TAG) {
            getWritableDB();
            dInfo.setStatus(DownloadStatus.STATUS_NORMAL);
            dInfo.setProgress(0);
            mDb.delete(DownLoadTable.TABLE_NAME, DownLoadTable._ID + " = ? ", new String[]{dInfo.get_id() + ""});
            dispose();
        }
    }

    /**
     * 清除所有数据
     */
    public static void cleanData() {
        synchronized (SYNC_TAG) {
            // mDb.delete(DownLoadTable.TABLE_NAME, null, null);
            List<DownLoadInfo> completeDownList = queryCompletedList();
            for (int i = 0; i < completeDownList.size(); i++) {
                getWritableDB();
                DownLoadInfo dInfo = completeDownList.get(i);
                dInfo.setStatus(DownloadStatus.STATUS_NORMAL);
                dInfo.setProgress(0);
//                mDb.update(DownLoadTable.TABLE_NAME, getContentValue(dInfo),
//                        DownLoadTable._ID + " = ? ",
//                        new String[]{dInfo.get_id() + ""});
//                mDb.delete()
                mDb.delete(DownLoadTable.TABLE_NAME, DownLoadTable._ID + " = ? ", new String[]{dInfo.get_id() + ""});
                dispose();
            }
        }
    }

    /**
     * 关闭数据库
     */
    public static synchronized void dispose() {
        if (mDb != null) {
            mDb.close();
        }
        if (helper != null) {
            helper.close();
        }

    }

    /**
     *
     *
     */
    public static void logOut() {
        synchronized (SYNC_TAG) {
            mDb = null;
            helper = null;
        }
    }
}
